﻿using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Linq;
using Dentiste.Domain.Entities;
using Dentiste.Domain.Interfaces;

namespace Dentiste.Domain.Providers
{
    internal class InamiProvider : IProvider<IInami>
    {
        private readonly string _connectionString;

        #region Constructor

        public InamiProvider(string connectionString)
        {
            _connectionString = connectionString;
        }

        #endregion

        #region Select

        public DbSetCustom<IInami> Select()
        {
            const string queryString = "SELECT * FROM Inamis";
            var ret = new DbSetCustom<IInami>(this);
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    ret.Add(new Inami
                        {
                            Code = reader.GetString(0),
                            Label = reader.GetString(1),
                            Prix1 = reader.GetDouble(2),
                            Prix2 = reader.GetDouble(3),
                            Prix3 = reader.GetDouble(4)
                        });
                }
            }
            return BuildRequiredLinks(ret);
        }

        public IInami Select(string id)
        {
            const string queryString = "SELECT * FROM Inamis where Code=?";
            IInami i = null;
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Code", id);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.Read())
                {
                    i = new Inami
                        {
                            Code = reader.GetString(0),
                            Label = reader.GetString(1),
                            Prix1 = reader.GetDouble(2),
                            Prix2 = reader.GetDouble(3),
                            Prix3 = reader.GetDouble(4)
                        };
                }
            }
            return BuildRequiredLinks(i);
        }

        private DbSetCustom<IInami> BuildRequiredLinks(DbSetCustom<IInami> list)
        {
            const string queryString = "SELECT * FROM InamisRequired";
            var items = new List<KeyValuePair<string, string>>();
            using (
                var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    items.Add(new KeyValuePair<string, string>(reader.GetString(0), reader.GetString(1)));
                }
            }
            foreach (IInami inami in list)
            {
                IInami inami1 = inami;
                IEnumerable<string> requiredId = from i in items where i.Key == inami1.Code select i.Value;
                foreach (var id in requiredId)
                {
                    inami.RequiredInamis.Add((from i in list where i.Code == id select i).First());
                }
            }
            return list;
        }

        public IInami BuildRequiredLinks(IInami inami)
        {
            if (inami == null)
                throw new Exception("Cannot build links. Inami cannot be null.");

            const string queryString = "SELECT * FROM InamisRequired";
            var items = new List<KeyValuePair<string, string>>();
            using (
                var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                connection.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();
                while (reader != null && reader.Read())
                {
                    items.Add(new KeyValuePair<string, string>(reader.GetString(0), reader.GetString(1)));
                }
            }
            IEnumerable<string> requiredId = from i in items where i.Key == inami.Code select i.Value;
            DbSetCustom<IInami> list = Select();
            foreach (var id in requiredId)
            {
                inami.RequiredInamis.Add((from i in list where i.Code == id select i).First());
            }
            return inami;
        }

        public IInami SelectLastInserted()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Insert

        public IInami Insert(IInami inami)
        {
            int ret;
            const string queryString = "INSERT INTO Inamis VALUES (?, ?, ?, ?, ?)";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Code", inami.Code);
                cmd.Parameters.AddWithValue("@Label", inami.Label);
                cmd.Parameters.AddWithValue("@Prix1", inami.Prix1);
                cmd.Parameters.AddWithValue("@Prix2", inami.Prix2);
                cmd.Parameters.AddWithValue("@Prix3", inami.Prix3);

                ret = cmd.ExecuteNonQuery();
            }
            return (ret == 1 ? inami : null);
        }

        public void LinkRequiredInami(IInami inamiParent, IInami inamiChild)
        {
            const string queryString = "INSERT INTO InamisRequired VALUES (?,?)";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@ParentId", inamiParent.Code);
                cmd.Parameters.AddWithValue("@ChildId", inamiChild.Code);
                connection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        #endregion

        #region Update

        public bool Update(IInami inami)
        {
            int ret;
            const string queryString = "UPDATE Inamis SET Label=?, Prix1=?, Prix2=?, Prix3=? WHERE Code=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Label", inami.Label);
                cmd.Parameters.AddWithValue("@Prix1", inami.Prix1);
                cmd.Parameters.AddWithValue("@Prix2", inami.Prix2);
                cmd.Parameters.AddWithValue("@Prix3", inami.Prix3);
                cmd.Parameters.AddWithValue("@Code", inami.Code);

                ret = cmd.ExecuteNonQuery();
            }
            return ret == 1;
        }

        #endregion

        #region Delete

        public bool Delete(IInami inami)
        {
            int ret;
            const string queryString = "DELETE FROM Inamis WHERE Code=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@Code", inami.Code);

                ret = cmd.ExecuteNonQuery();
            }
            return ret == 1;
        }

        public void UnlinkRequiredInami(IInami inamiParent, IInami inamiChild)
        {
            const string queryString = "DELETE FROM InamisRequired WHERE ParentId=? AND ChildId=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@ParentId", inamiParent.Code);
                cmd.Parameters.AddWithValue("@ChildId", inamiChild.Code);
                connection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public void UnlinkRequiredInami(IInami inamiParent)
        {
            const string queryString = "DELETE FROM InamisRequired WHERE ParentId=?";
            using (var connection = new SqlCeConnection(_connectionString))
            {
                var cmd = new SqlCeCommand(queryString, connection);
                cmd.Parameters.AddWithValue("@ParentId", inamiParent.Code);
                connection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        #endregion
    }
}